package com.cq;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.web.multipart.MultipartFile;

public class ImportExcelUntil {   
        
    public static List<Map<String, Object>> importExcel(MultipartFile file) throws Exception {    
         List<String> columnList = new ArrayList<String>();
        //装载流    
        POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());    //excel写入
        //获取excel表格
        HSSFWorkbook hw= new HSSFWorkbook(fs);    
            
        //获取第一个sheet页    
        HSSFSheet sheet = hw.getSheetAt(0);   
        
        //获取标题集合
        HSSFRow titleRow= sheet.getRow(0);//标题行
        int columnNum = titleRow.getPhysicalNumberOfCells();//获取总列数
        for(int i = 0; i < columnNum; i++){
        	columnList.add(getVal(titleRow.getCell(i)));
        }
        
        //容器    
        List<Map<String, Object>> ret = new ArrayList<Map<String, Object>>();    
        System.out.println(sheet.getLastRowNum());  //打印真实行数
        //遍历行 从下标第一行开始（去除标题）   
        for (int i = 1; i < sheet.getLastRowNum()+1; i++) {    
            HSSFRow row= sheet.getRow(i);    
            if(row!=null){       
            	ret.add(dataObj(columnList,row));    
            }    
        }    
        return ret;    
    }    
    
	private static Map<String, Object>  dataObj(List<String> columnList,HSSFRow row) throws Exception {      
        //容器    
        Map<String, Object> map = new HashMap<String, Object>();    
            
        //注意excel表格字段顺序要和obj字段顺序对齐 （如果有多余字段请另作特殊下标对应处理）    
        for (int j = 0; j < columnList.size(); j++) {    
            map.put(columnList.get(j), getVal(row.getCell(j)));//封装字段（key）与值(value)    
        }    
        return map;     
    } 
    
    /**  
     * 处理val
     * @param hssfCell  
     * @return  
     */    
    @SuppressWarnings("static-access")
	public static String getVal(HSSFCell hssfCell) {    
    	if (null == hssfCell) {
			return "";
		}
		if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
			// 返回布尔类型的值
			return String.valueOf(hssfCell.getBooleanCellValue());
		} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
			//返回时间类型
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				Date d = hssfCell.getDateCellValue();
				SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				return df.format(d);
			}else{
				// 返回数值类型的值
				Double temp = hssfCell.getNumericCellValue();
				BigDecimal bd = new BigDecimal(temp);
				return String.valueOf(bd.toString());
			}
		} else {
			// 返回字符串类型的值
			return String.valueOf(hssfCell.getStringCellValue());
		} 
    }    
}
